package com.mhr.dao;

import com.mhr.domain.Book;
import com.mhr.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class BookDao {

    //插入
    public boolean insert(Book book) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            String sql = "INSERT INTO Book VALUES(null,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            //插入信息
            ps.setString(1, book.getBookName());
            ps.setString(2, book.getAuthor());
            ps.setString(3, book.getPress());
            ps.setString(4, book.getIsbn());
            ps.setString(5, book.getCategory());
            ps.setFloat(6, book.getPrice());

            int num = ps.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(null,ps,conn);
        }
        return false;
    }

    //修改
    public boolean update(Book book){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            String sql = "UPDATE Book SET bookName=?,author=?,press=?,isbn=?,category=?,price=? where id=?";
            ps = conn.prepareStatement(sql);
            //插入信息
            ps.setString(1, book.getBookName());
            ps.setString(2, book.getAuthor());
            ps.setString(3, book.getPress());
            ps.setString(4, book.getIsbn());
            ps.setString(5, book.getCategory());
            ps.setFloat(6, book.getPrice());
            ps.setInt(7,book.getId());

            int num = ps.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(null,ps,conn);
        }
        return false;
    }
    //删除
    public boolean delete(int id){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 获得数据的连接
            conn = JDBCUtils.getConnection();
            String sql = "DELETE FROM Book WHERE id = ?";
            ps = conn.prepareStatement(sql);
            //删除信息
            ps.setInt(1, id);

            int num = ps.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(null,ps,conn);
        }
        return false;
    }

    //查询
    public Book find(int id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Book book = null;
        // 获得数据的连接
        try {
            conn = JDBCUtils.getConnection();
            // SQL 语句
            String sql = "SELECT * FROM Book WHERE id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            // 生成结果集
            rs = ps.executeQuery();
            // 处理结果集
            while (rs.next())
            {
                book = new Book();
                book.setId(rs.getInt("id"));
                book.setBookName(rs.getString("bookName"));
                book.setAuthor(rs.getString("author"));
                book.setPress(rs.getString("press"));
                book.setIsbn(rs.getString("isbn"));
                book.setCategory(rs.getString("category"));
                book.setPrice(rs.getFloat("price"));
                return book;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.release(rs,ps,conn);
        }
        return null;
    }
    //查询全部
    public ArrayList<Book> listAll(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        // 获得数据的连接
        try {
            conn = JDBCUtils.getConnection();
            // SQL 语句
            String sql = "SELECT * FROM Book";
            ps = conn.prepareStatement(sql);
            // 生成结果集
            rs = ps.executeQuery();
            // 处理结果集
            ArrayList<Book> list = new ArrayList<Book>();
            while (rs.next())
            {
                //创建对象
                Book book = new Book();
                //填充对象
                book.setId(rs.getInt("id"));
                book.setBookName(rs.getString("bookName"));
                book.setAuthor(rs.getString("author"));
                book.setPress(rs.getString("press"));
                book.setIsbn(rs.getString("isbn"));
                book.setCategory(rs.getString("category"));
                book.setPrice(rs.getFloat("price"));
                //对象加入到列表
                list.add(book);
            }
            return list;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.release(rs,ps,conn);
        }
        return null;
    }
    
}
